package com.xiaoy.core.database.service;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.transform.Transformers;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springside.modules.orm.PropertyFilter;

import com.xiaoy.core.database.entity.Column;
import com.xiaoy.core.database.entity.DataTable;
import com.xiaoy.core.database.entity.Sequence;
import com.xiaoy.dao.DaoUtil;
import com.xiaoy.util.StringUtil;

@Service("databaseService")
@Transactional
public class DatabaseService {
	
	private static final Log logger = LogFactory.getLog(DatabaseService.class);
	
	@Resource(name="daoUtil")
	private DaoUtil dUtil;
	
	public List<DataTable> getDataTables () {
		
		String sql = 
		//""
		//"SELECT tc.table_name TABLENAME, tc.comments TABLEINFO FROM USER_TAB_COMMENTS tc where table_name like 'B%' order by tc.table_name";
		"SELECT tc.table_name TABLENAME, tc.comments TABLEINFO FROM USER_TAB_COMMENTS tc "
		//+ " where table_name like 'B%' "
		+ " order by tc.table_name"
		;
		List<Map<String,Object>>list=dUtil.query(sql);
		return toDataTables(list);
	}
	
	public List<DataTable> getDataTables (List<PropertyFilter>params){
		
		String sql ="(SELECT tc.table_name TABLENAME, tc.comments TABLEINFO FROM USER_TAB_COMMENTS tc)";
//		List<Map<String,Object>>list=dUtil.query(sql,params);
		List<Map<String,Object>>list=dUtil.execute(sql, params);
		return toDataTables(list);
	}
	
	@Transactional
	public void createTable(DataTable dataTable) {
		String ta = dataTable.toTable();
		dUtil.saveOrUpdate(ta);
		logger.info("创建表 ："+dataTable.getName());
		if (dataTable.getSequence()!=null) {
			createSequence(dataTable.getSequence());
			logger.info("创建 "+dataTable.getName()+"的序列："+dataTable.getSequence().getName());
		}
	}
	@Transactional
	public void createSequence(Sequence sequence) {
		dUtil.saveOrUpdate(sequence.toString());
		logger.info("创建 序列："+sequence.getName());
	}
	//List<Column>
	public DataTable getDBTableInfo (String tablename) {
		
		String sql=
			" SELECT "+ 
			" u.COLUMN_NAME name , "+
			" u.DATA_TYPE type , "+
			" u.DATA_LENGTH length  , "+
			" u.NULLABLE isNull , "+
			" cc.comments info , "+
			" tc.table_name tablename, "+
			" tc.comments tableInfo "+
			" FROM USER_TAB_COLS u, USER_COL_COMMENTS cc,USER_TAB_COMMENTS tc "+
			" where u.TABLE_NAME = cc.TABLE_NAME and u.TABLE_NAME=tc.TABLE_NAME  "+
			" and cc.COLUMN_NAME = u.COLUMN_NAME and tc.TABLE_NAME = '"+tablename+"'";
		
		DataTable dt = new DataTable();
		Session session = dUtil.getSession();
		SQLQuery query = session.createSQLQuery(sql);
		query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		List<Map<String,Object>>maps= query.list();
		if(maps.size()>0) {
			Map<String,Object> table = maps.get(0);
			dt.setName(StringUtil.toString(table.get("TABLENAME")));
			dt.setInfo(StringUtil.toString(table.get("TABLEINFO")));
		}
		dt.setColumns(toColumns(maps));
		return dt;
	}
	
	public List<Column>getDBColumnsByTablename (String tablename) {
		String sql = " SELECT "+
			" u.COLUMN_NAME name ,"+
			" u.DATA_TYPE type ,"+
			" u.DATA_LENGTH length  ,"+
			" u.NULLABLE isNull ,"+
			" cc.comments info "+
			" FROM USER_TAB_COLS u, USER_COL_COMMENTS cc"+
			" where u.TABLE_NAME = cc.TABLE_NAME "+
			" and cc.COLUMN_NAME = u.COLUMN_NAME and u.TABLE_NAME = '"+tablename+"' order by name asc";
		Session session = dUtil.getSession();
		SQLQuery query = session.createSQLQuery(sql);
		query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		List<Map<String,Object>>maps= query.list();
		return toColumns(maps);
	}
	
	//  得到数据库的内容
//	private void getDBTableInfoByTablename(String tablename){
//		
//		Session session = dUtil.getSession();
//		try {
//			
//			 Connection conn = session.connection();
////			 conn.setCatalog("basqqq");
//			 PreparedStatement preparedStatement = conn.prepareStatement("select * from BAS_BPM_PROCESS_PROCESS");
//		     
//			 // 备注
//			 PreparedStatement preparedStatement2 = conn.prepareStatement("select * from user_col_comments where Table_name = BAS_BPM_PROCESS_PROCESS");
//			 
//		     // 获取结果
//		     ResultSet resultSet = preparedStatement.executeQuery();
//		     
//		     // 获取各个列的信息
//		     ResultSetMetaData data = resultSet.getMetaData();
//		     resultSet.getRow();
//		     resultSet.next() ;
//		     System.out.println("comment-->"+ resultSet.getString("comment"));
//	    	 for (int i = 1; i <= data.getColumnCount(); i++) {
//    		    // 打印列名
//    		    System.out.println(data.getTableName(i)+
//    		    		"-->"+data.getColumnName(i)+
//    		    		"-->"+data.getColumnTypeName(i)+
//    		    		"-->"+data.getCatalogName(i)+
//    		    		"-->"+data.getColumnClassName(i)+
//    		    		"-->"+data.getColumnLabel(i)+
//    		    		"-->"+data.getColumnDisplaySize (i)) ;
//    		    		;
//		     }
//	    	 System.out.println("======================================================================");
//	    	 
//	    	 for (int i = 1; i <= data.getColumnCount(); i++) {
//		    		//获得所有列的数目及实际列数
//		    		 int columnCount=data.getColumnCount();
//		    		 //获得指定列的列名
//		    		 String columnName = data.getColumnName(i);
//		    		 //获得指定列的列值
////		    		 String columnValue = rs.getString(i);
//		    		 //获得指定列的数据类型
//		    		 int columnType=data.getColumnType(i);
//		    		 //获得指定列的数据类型名
//		    		 String columnTypeName=data.getColumnTypeName(i);
//		    		 //所在的Catalog名字
//		    		 String catalogName=data.getCatalogName(i);
//		    		 //对应数据类型的类
//		    		 String columnClassName=data.getColumnClassName(i);
//		    		 //在数据库中类型的最大字符个数
//		    		 int columnDisplaySize=data.getColumnDisplaySize(i);
//		    		 //默认的列的标题
//		    		 String columnLabel=data.getColumnLabel(i);
//		    		 //获得列的模式
//		    		 String schemaName=data.getSchemaName(i);
//		    		 //某列类型的精确度(类型的长度)
//		    		 int precision= data.getPrecision(i);
//		    		 //小数点后的位数
//		    		 int scale=data.getScale(i);
//		    		 //获取某列对应的表名
//		    		 String tableName=data.getTableName(i);
//		    		 // 是否自动递增
//		    		 boolean isAutoInctement=data.isAutoIncrement(i);
//		    		 //在数据库中是否为货币型
//		    		 boolean isCurrency=data.isCurrency(i);
//		    		 //是否为空
//		    		 int isNullable=data.isNullable(i);
//		    		 //是否为只读
//		    		 boolean isReadOnly=data.isReadOnly(i);
//		    		 //能否出现在where中
//		    		 boolean isSearchable=data.isSearchable(i);
//		    		 System.out.println(columnCount);
//		    		 System.out.println("获得列"+i+"的字段名称:"+columnName);
////		    		 System.out.println("获得列"+i+"的字段值:"+columnValue);
//		    		 System.out.println("获得列"+i+"的类型,返回SqlType中的编号:"+columnType);
//		    		 System.out.println("获得列"+i+"的数据类型名:"+columnTypeName);
//		    		 System.out.println("获得列"+i+"所在的Catalog名字:"+catalogName);
//	    		     System.out.println("获得列"+i+"对应数据类型的类:"+columnClassName);
//	    		     System.out.println("获得列"+i+"在数据库中类型的最大字符个数:"+columnDisplaySize);
//	    		     System.out.println("获得列"+i+"的默认的列的标题:"+columnLabel);
//	    		     System.out.println("获得列"+i+"的模式:"+schemaName);
//	    		     System.out.println("获得列"+i+"类型的精确度(类型的长度):"+precision);
//	    		     System.out.println("获得列"+i+"小数点后的位数:"+scale);
//	    		     System.out.println("获得列"+i+"对应的表名:" + tableName);
//	    		     System.out.println("获得列"+i+"是否自动递增:"+isAutoInctement);
//	    		     System.out.println("获得列"+i+"在数据库中是否为货币型:"+isCurrency);
//	    		     System.out.println("获得列"+i+"是否为空:"+isNullable);
//	    		     System.out.println("获得列"+i+"是否为只读:"+isReadOnly);
//	    		     System.out.println("获得列"+i+"能否出现在where中:"+isSearchable);  
//			     }
//		} catch (Exception e) {
//			e.printStackTrace();
//		}
//	}
	
	
	private String[] fields = {"name","","",""};
	private String[] fieldsInfo = {"名称","注解","长度","是否null","类型","默认值"};
//	public static String xlsFile="f:/test.xls"; //产生的Excel文件的名称
	
	public void test () {System.out.println("fuck");}
	
	
	public void expTable (String absolutePath) throws Exception {
		
	}
	
	
	
	
	
	
	
	
	
	//-------------------------------------------------------------------------------------
	private Column toColumn (Map<String,Object>map ) {
		Column c = new Column();
		c.setLength(StringUtil.toString(map.get("LENGTH")));
		c.setName(StringUtil.toString(map.get("NAME")));
		c.setType(StringUtil.toString(map.get("TYPE")));
		c.setInfo(StringUtil.toString(map.get("INFO")));
		c.setIsNull(StringUtil.toString(map.get("ISNULL")));
		return c;
	}
	private List<Column> toColumns (List<Map<String,Object>>maps ) {
		List<Column> list = new ArrayList<Column>();
		Column c = null;
		for (Map<String,Object>map:maps) {
			c = toColumn(map);
			list.add(c);
			c = null;
		}
		return list;
	}
	
	private DataTable toDataTable (Map<String,Object>map) {
		DataTable table = new DataTable();
		table.setName(StringUtil.toString(map.get("TABLENAME")));
		table.setInfo(StringUtil.toString(map.get("TABLEINFO")));
		return table;
	}
	private List<DataTable>toDataTables (List<Map<String,Object>>maps) {
		List<DataTable>tables = new ArrayList<DataTable>();
		for (Map<String,Object>map:maps) {
			tables.add(toDataTable(map));
		}
		return tables;
	}
	
	
}
